python Read and Write excel File Operation Example [Source Download Attached]

This paper gives an example of how python reads and writes excel files.Share it for your reference, as follows:

python has third-party toolkit support for the operation of excel files, xlutils, which includes toolkits such as xlrd and xlwt. With these tools, excel can be easily operated on.

1. Download xlutils: http://pypi.python.org/pypi/xlutils

2. Install, after unzipping the download file, you can python setup.py install

3. Application (generating EXCEL, traversing EXCEL, modifying EXCEL, attribute control, date control, etc.).

1) Create EXCEL file

from tempfile import TemporaryFile
from xlwt import Workbook
book = Workbook()
sheet1 = book.add_sheet('Sheet 1')
book.add_sheet('Sheet 2')
sheet1.write(0,0,'A1')
sheet1.write(0,1,'B1')
row1 = sheet1.row(1)
row1.write(0,'A2')
row1.write(1,'B2')
sheet1.col(0).width = 10000
sheet2 = book.get_sheet(1)
sheet2.row(0).write(0,'Sheet 2 A1')
sheet2.row(0).write(1,'Sheet 2 B1')
sheet2.flush_row_data()
sheet2.write(1,0,'Sheet 2 A3')
sheet2.col(0).width = 5000
sheet2.col(0).hidden = True
book.save('simple.xls')
book.save(TemporaryFile())

This generates the simple.xls file.

2) Loop through EXCEL files

import xlrd
import xlutils.copy
import os
if __name__ == '__main__':
  wb = xlrd.open_workbook('simple.xls')  
  for s in wb.sheets():
    print 'Sheet:',s.name
    for row in range(s.nrows):
      values = []
      for col in range(s.ncols):
        values.append(s.cell(row,col).value)
      print ','.join(values)
    print

Traverse the entire excel and print out the data

3) Modify EXCEL

import xlrd
import xlutils.copy
import os
if __name__ == '__main__':
  template = "simple.xls"
  workBook = xlrd.open_workbook(template,formatting_info=True)
  workBook = xlutils.copy.copy(workBook)
  sheet = workBook.get_sheet(0)
  sheet.write(0, 0, '111')
  sheet.write(0, 1, '222')
  sheet.write(1, 0, '333')
  sheet.write(1, 1, '444')  
  workBook.save('simple.xls')

Full sample code click here to download.

More readers interested in Python-related content can view this site's topics: Python Operation Excel Table Skills Summary, Python File and Directory Operation Skills Summary, Python Text File Operation Skills Summary, Python Data Structure and Algorithms Tutorial, Python Function Use Skills Summary, etc.Summary of Python String Operation Techniques and Python Introduction and Advanced Classic Tutorials

I hope that the description in this paper will be helpful to everyone's Python program design.